﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MedicareModel;
using MySql.Data.MySqlClient;

namespace MedicareDataAccess
{
    public class PatientDAO : DAOConnection
    {
        public static PatientDAO GetInstance()
        {
            return new PatientDAO();
        }

        public Patient getPatientByID(int idPatient)
        {
            MySqlCommand cm = DBGetCommand(string.Format("SELECT * FROM patients WHERE idPatient={0}", idPatient));
            MySqlDataReader dr = cm.ExecuteReader();
            if (!dr.Read())
            {
                cm.Connection.Close();
                return null;
            }

            Patient aPatient = new Patient(); 
            aPatient.idPatient = (int)dr["idPatient"];
            aPatient.name = (string)dr["name"];
            aPatient.lastName = (string)dr["lastName"];
            aPatient.ssn = (string)dr["ssn"];
            aPatient.dob = (DateTime)dr["dob"];
            aPatient.address = (string)dr["address"];
            aPatient.phone = (string)dr["phone"];
            aPatient.processed = (bool)dr["processed"];
            aPatient.emtTeamSent = (bool)dr["emtTeamSent"];
            cm.Connection.Close();
            return aPatient;
        }


        public List<Patient> getAllPatients()
        {
            List<Patient> patients = new List<Patient>(); 
            MySqlCommand cm = DBGetCommand(string.Format("SELECT * FROM patients"));
            MySqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                Patient aPatient = new Patient(); 
                aPatient.idPatient = (int)dr["idPatient"];
                aPatient.name = (string)dr["name"];
                aPatient.lastName = (string)dr["lastName"];
                aPatient.ssn = (string)dr["ssn"];
                aPatient.dob = (DateTime)dr["dob"];
                aPatient.address = (string)dr["address"];
                aPatient.phone = (string)dr["phone"];
                aPatient.processed = (bool)dr["processed"];
                aPatient.emtTeamSent = (bool)dr["emtTeamSent"];
                patients.Add(aPatient);
            }

            cm.Connection.Close();
            return patients;
        }

        public List<Patient> getPendingPatients()
        {
            List<Patient> patients = new List<Patient>(); 
            MySqlCommand cm = DBGetCommand(string.Format("SELECT * FROM patients"));
            MySqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                if (!(bool)dr["processed"])
                {
                    Patient aPatient = new Patient();
                    aPatient.idPatient = (int)dr["idPatient"];
                    aPatient.name = (string)dr["name"];
                    aPatient.lastName = (string)dr["lastName"];
                    aPatient.ssn = (string)dr["ssn"];
                    aPatient.dob = (DateTime)dr["dob"];
                    aPatient.address = (string)dr["address"];
                    aPatient.phone = (string)dr["phone"];
                    aPatient.processed = (bool)dr["processed"];
                    aPatient.emtTeamSent = (bool)dr["emtTeamSent"];
                    patients.Add(aPatient);
                }
            }

            cm.Connection.Close();
            return patients;
        }

        public List<Patient> getTreatedPatients()
        {
            List<Patient> patients = new List<Patient>(); 
            MySqlCommand cm = DBGetCommand(string.Format("SELECT * FROM patients"));
            MySqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                if ((bool)dr["processed"] && !(bool)dr["emtTeamSent"])
                {
                    Patient aPatient = new Patient();
                    aPatient.idPatient = (int)dr["idPatient"];
                    aPatient.name = (string)dr["name"];
                    aPatient.lastName = (string)dr["lastName"];
                    aPatient.ssn = (string)dr["ssn"];
                    aPatient.dob = (DateTime)dr["dob"];
                    aPatient.address = (string)dr["address"];
                    aPatient.phone = (string)dr["phone"];
                    aPatient.emtTeamSent = (bool)dr["emtTeamSent"];
                    patients.Add(aPatient);
                }
            }

            cm.Connection.Close();
            return patients;
        }

        public void createPatient(Patient aPatient)
        {
            string dob = aPatient.dob.ToString("yyyy-MM-dd");
            string query = string.Format("INSERT INTO patients (name, lastName, ssn, dob, address, phone)" + "VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", aPatient.name, aPatient.lastName, aPatient.ssn, dob, aPatient.address, aPatient.phone);
            MySqlCommand cm = DBGetCommand(query);
            cm.ExecuteNonQuery();
        }

        public void updatePatient(Patient aPatient)
        {
            string dob = aPatient.dob.ToString("yyyy-MM-dd");
            string query = string.Format("UPDATE patients SET name = '{0}', lastName = '{1}', ssn = '{2}', dob = '{3}', address = '{4}', phone = '{5}' WHERE idPatient = '{6}'", aPatient.name, aPatient.lastName, aPatient.ssn, dob, aPatient.address, aPatient.phone, aPatient.idPatient);
            MySqlCommand cm = DBGetCommand(query);
            cm.ExecuteNonQuery();
        }


        public void deletePatient(int idPatient)
        {
            string query = "DELETE FROM patients WHERE idPatient = " + idPatient;
            MySqlCommand cm = DBGetCommand(query);
            cm.ExecuteNonQuery();
            cm.Connection.Close();
        }

        public void requestProcessed(int idPatient)
        {
            string query = string.Format("UPDATE patients SET processed = 1 WHERE idPatient = '{0}'", idPatient);
            MySqlCommand cm = DBGetCommand(query);
            cm.ExecuteNonQuery();
        }

        public void helpOnTheWay(int idPatient)
        {
            string query = string.Format("UPDATE patients SET emtTeamSent = 1 WHERE idPatient = '{0}'", idPatient);
            MySqlCommand cm = DBGetCommand(query);
            cm.ExecuteNonQuery();
        }

        public List<Patient> getByLastName(String aLastName)
        {
            List<Patient> patients = new List<Patient>();
            MySqlCommand cm = DBGetCommand(string.Format("SELECT * FROM patients WHERE lastName LIKE '%{0}%'", aLastName));
            MySqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                if (!(bool)dr["processed"])
                {
                    Patient aPatient = new Patient();
                    aPatient.idPatient = (int)dr["idPatient"];
                    aPatient.name = (string)dr["name"];
                    aPatient.lastName = (string)dr["lastName"];
                    aPatient.ssn = (string)dr["ssn"];
                    aPatient.dob = (DateTime)dr["dob"];
                    aPatient.address = (string)dr["address"];
                    aPatient.phone = (string)dr["phone"];
                    aPatient.processed = (bool)dr["processed"];
                    aPatient.emtTeamSent = (bool)dr["emtTeamSent"];
                    patients.Add(aPatient);
                }
            }

            cm.Connection.Close();
            return patients;
        }

        public List<Patient> getByDOB(String aDOB)
        {
            List<Patient> patients = new List<Patient>();
            MySqlCommand cm = DBGetCommand(string.Format("SELECT * FROM patients WHERE dob LIKE '%{0}%'", aDOB));
            MySqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                if (!(bool)dr["processed"])
                {
                    Patient aPatient = new Patient();
                    aPatient.idPatient = (int)dr["idPatient"];
                    aPatient.name = (string)dr["name"];
                    aPatient.lastName = (string)dr["lastName"];
                    aPatient.ssn = (string)dr["ssn"];
                    aPatient.dob = (DateTime)dr["dob"];
                    aPatient.address = (string)dr["address"];
                    aPatient.phone = (string)dr["phone"];
                    aPatient.processed = (bool)dr["processed"];
                    aPatient.emtTeamSent = (bool)dr["emtTeamSent"];
                    patients.Add(aPatient);
                }
            }

            cm.Connection.Close();
            return patients;
        }

        public List<Patient> getBySSN(String aSSN)
        {
            List<Patient> patients = new List<Patient>();
            MySqlCommand cm = DBGetCommand(string.Format("SELECT * FROM patients WHERE ssn LIKE '%{0}%'", aSSN));
            MySqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                if (!(bool)dr["processed"])
                {
                    Patient aPatient = new Patient();
                    aPatient.idPatient = (int)dr["idPatient"];
                    aPatient.name = (string)dr["name"];
                    aPatient.lastName = (string)dr["lastName"];
                    aPatient.ssn = (string)dr["ssn"];
                    aPatient.dob = (DateTime)dr["dob"];
                    aPatient.address = (string)dr["address"];
                    aPatient.phone = (string)dr["phone"];
                    aPatient.processed = (bool)dr["processed"];
                    aPatient.emtTeamSent = (bool)dr["emtTeamSent"];
                    patients.Add(aPatient);
                }
            }

            cm.Connection.Close();
            return patients;
        }

        public void resetPatient(int aPatientID)
        {
            string query = string.Format("UPDATE patients SET processed = 0, emtTeamsent = 0 WHERE idPatient = '{0}'", aPatientID);
            MySqlCommand cm = DBGetCommand(query);
            cm.ExecuteNonQuery();
        }

        public Boolean patientHasAssignedEvents(int aPatientID)
        {
            return false; 
        }
    }
}
